package Dao.Role.Imp;

import Dao.Role.TeacherDao;
import Entity.Role.Teacher;
import Util.DBUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class TeacherDaoImp implements TeacherDao {

    private PreparedStatement ps = null;
    private ResultSet rs = null;

    /**
     * 通过工号查询老师信息
     *
     * @param tNO 老师的工号
     * @return teacher列表
     */
    public List<Teacher> getTeacherByNO(String tNO, Connection conn) {
        List<Teacher> teachers = new ArrayList<>();
        try {
            String sql = "select tno,password,name,sex,post,tel,email,collegeid,department,role, email_code " +
                    "from teacher where tno = ?";
            ps = conn.prepareStatement(sql);
            ps.setString(1, tNO);
            rs = ps.executeQuery();
            while (rs.next()) {
                String NO = rs.getString("tno");
                String password = rs.getString("password");
                String name = rs.getString("name");
                String sex = rs.getString("sex");
                String post = rs.getString("post");
                String tel = rs.getString("tel");
                String email = rs.getString("email");
                String collId = rs.getString("collegeid");
                String department = rs.getString("department");
                String role = rs.getString("role");
                String emailCode = rs.getString("email_code");
                Teacher teacher = new Teacher(NO, password, name, sex, post, tel, email, collId, department, role, emailCode);
                teachers.add(teacher);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.closeMySQLResource(ps, rs);
        }
        return teachers;
    }

    @Override
    public Integer updatePassByTNO(String TNO, String newPass, Connection conn) {
        int res = 0;
        try {
            String sql = "update teacher set password = ? where tno = ? ";
            ps = conn.prepareStatement(sql);
            ps.setString(1, newPass);
            ps.setString(2, TNO);
            res = ps.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.closeMySQLResource(ps, rs);
        }
        return res;
    }

    @Override
    public Integer updateEmailByNO(String NO, String newEmail, Connection conn) {
        int res = 0;
        try {
            String sql = "update teacher set email = ? where tno = ? ";
            ps = conn.prepareStatement(sql);
            ps.setString(1, newEmail);
            ps.setString(2, NO);
            res = ps.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.closeMySQLResource(ps, rs);
        }
        return res;
    }

    @Override
    public Integer updateEmailCodeByNO(String NO, String emailCode, Connection conn) {
        int res = 0;
        try {
            String sql = "update teacher set email_code = ? where tno = ? ";
            ps = conn.prepareStatement(sql);
            ps.setString(1, emailCode);
            ps.setString(2, NO);
            res = ps.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.closeMySQLResource(ps, rs);
        }
        return res;
    }

    @Override
    public Integer importImpTeacher(Teacher teacher, Connection conn) {
        int res = 0;
        try {
            String sql = "insert into teacher (tno,password,name,sex,post,tel,email,collegeid,department) " +
                    " values (?,?,?,?,?,?,?,?,?)";
            ps = conn.prepareStatement(sql);
            ps.setString(1, teacher.getTNO());
            ps.setString(2, teacher.getPassword());
            ps.setString(3, teacher.getName());
            ps.setString(4, teacher.getSex());
            ps.setString(5, teacher.getPost());
            ps.setString(6, teacher.getTel());
            ps.setString(7, teacher.getEmail());
            ps.setString(8, teacher.getCollegeId());
            ps.setString(9, teacher.getDepartment());
            res = ps.executeUpdate();
        } catch (SQLException e) {
            String sql1 = "update teacher " +
                    "   set password = ?,name = ?,sex = ?,post = ?,tel = ?,email = ?,collegeid = ?,department = ? " +
                    "  where tno = ?";
            try {
                ps = conn.prepareStatement(sql1);
                ps.setString(1, teacher.getPassword());
                ps.setString(2, teacher.getName());
                ps.setString(3, teacher.getSex());
                ps.setString(4, teacher.getPost());
                ps.setString(5, teacher.getTel());
                ps.setString(6, teacher.getEmail());
                ps.setString(7, teacher.getCollegeId());
                ps.setString(8, teacher.getDepartment());
                ps.setString(9, teacher.getTNO());
                res = ps.executeUpdate();
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
        } finally {
            DBUtil.closeMySQLResource(ps, rs);
        }
        return res;
    }
}
